package com.mathlearning.auth.repository;

import com.mathlearning.auth.entity.Discussion;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface DiscussionRepository extends JpaRepository<Discussion, Long>, JpaSpecificationExecutor<Discussion> {
    
    // 按热度排序（点赞数+评论数+收藏数）
    @Query("SELECT d FROM Discussion d WHERE d.isDeleted = 0 " +
           "ORDER BY (d.likes + d.comments + d.favorites) DESC")
    Page<Discussion> findByHot(Pageable pageable);
    
    // 按最新排序
    @Query("SELECT d FROM Discussion d WHERE d.isDeleted = 0 ORDER BY d.createdAt DESC")
    Page<Discussion> findByLatest(Pageable pageable);
    
    // 按未回答排序
    @Query("SELECT d FROM Discussion d WHERE d.isDeleted = 0 ORDER BY d.comments ASC, d.createdAt DESC")
    Page<Discussion> findByUnanswered(Pageable pageable);
    
    // 关键词搜索（已废弃，使用Specification代替）
    @Deprecated
    @Query("SELECT d FROM Discussion d WHERE d.isDeleted = 0 " +
           "AND (d.title LIKE %:keyword% OR d.content LIKE %:keyword%) " +
           "ORDER BY (d.likes + d.comments + d.favorites) DESC")
    Page<Discussion> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);
    
    // 根据标签筛选（已废弃，使用Specification代替）
    @Deprecated
    @Query("SELECT d FROM Discussion d JOIN DiscussionTag dt ON d.id = dt.discussionId " +
           "WHERE d.isDeleted = 0 AND dt.tagName = :tag " +
           "ORDER BY (d.likes + d.comments + d.favorites) DESC")
    Page<Discussion> findByTag(@Param("tag") String tag, Pageable pageable);
    
    // 根据作者ID查询
    Page<Discussion> findByAuthorIdAndIsDeleted(Long authorId, Integer isDeleted, Pageable pageable);
    
    // 统计用户的总浏览数
    @Query("SELECT SUM(d.views) FROM Discussion d WHERE d.authorId = :authorId AND d.isDeleted = 0")
    Long sumViewsByAuthorId(@Param("authorId") Long authorId);
    
    // 统计用户的总点赞数
    @Query("SELECT SUM(d.likes) FROM Discussion d WHERE d.authorId = :authorId AND d.isDeleted = 0")
    Long sumLikesByAuthorId(@Param("authorId") Long authorId);
    
    // 统计用户的总收藏数
    @Query("SELECT SUM(d.favorites) FROM Discussion d WHERE d.authorId = :authorId AND d.isDeleted = 0")
    Long sumFavoritesByAuthorId(@Param("authorId") Long authorId);
    
    // 统计用户的总评论数
    @Query("SELECT SUM(d.comments) FROM Discussion d WHERE d.authorId = :authorId AND d.isDeleted = 0")
    Long sumCommentsByAuthorId(@Param("authorId") Long authorId);
    
    // 统计用户发帖数
    @Query("SELECT COUNT(d) FROM Discussion d WHERE d.authorId = :authorId AND d.isDeleted = 0")
    Long countByAuthorId(@Param("authorId") Long authorId);
    
    // 查询用户所有帖子（按时间倒序，不分页）
    @Query("SELECT d FROM Discussion d WHERE d.authorId = :authorId AND d.isDeleted = 0 ORDER BY d.createdAt DESC")
    java.util.List<Discussion> findAllByAuthorIdOrderByCreatedAtDesc(@Param("authorId") Long authorId);
}



